﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace FingerprintAttendance
{
    public  class DataOperation
    {
      public static DataTable GetUsers()
      {
          string sql = "select * from users";
          return DBHelper.GetDataSet(sql).Tables[0];

      }
      public static DataTable GetUsers(string UserId)
      {
          string sql = "select * from users where UserId='" + UserId + "'";
          return DBHelper.GetDataSet(sql).Tables[0];

      }

      public static int AddFingerprint(string userId, string fingerId, string userType, string userName, string userNumber, string fpEncode, byte[] fpImg)
      {
          string sql = string.Format("select FingerprintID from fingerprint where userId='{0}' and FingerId='{1}'", userId, fingerId);
          object obj= DBHelper.ExecuteScalar(sql);
         
          if (obj == null)
              sql = "insert into fingerprint (UserId,FingerId,UserType,UserName,UserNumber,Encode,Image) values (@UserId,@FingerId,@UserType,@UserName,@UserNumber,@Encode,@Image)";
          else
              sql = "update fingerprint set Encode=@Encode,Image=@Image where UserId=@UserId and FingerId=@FingerId ";

          SQLiteParameter[] param = new SQLiteParameter[7];
          param[0] = new SQLiteParameter("@UserId", userId);
          param[1] = new SQLiteParameter("@FingerId", fingerId);
          param[2] = new SQLiteParameter("@UserType", userType);
          param[3] = new SQLiteParameter("@UserName", userName);
          param[4] = new SQLiteParameter("@UserNumber", userNumber);
          param[5] = new SQLiteParameter("@Encode", fpEncode);
          param[6] = new SQLiteParameter("@Image");
          param[6].Value = fpImg;
          return DBHelper.ExecuteSqlWithParams(sql, param);
      }

      public static DataTable GetFingerprintByUser(string userId)
      {
          string sql = "select * from fingerprint where Userid='" + userId + "'";
          return DBHelper.GetDataSet(sql).Tables[0];

      }

      public static DataTable GetFingerprint()
      {
          string sql = "select FingerprintID,UserId,FingerId,Encode from FingerPrint";
          return DBHelper.GetDataSet(sql).Tables[0];

      }

      public static DataTable GetFingerprintById(int fingerprintID)
      {
          string sql = "select UserId,FingerId,UserType,UserName,UserNumber from FingerPrint where FingerprintID=" + fingerprintID.ToString();
          return DBHelper.GetDataSet(sql).Tables[0];

      }

      public static int DelFingerprintById(int fingerprintID)
      {
          string sql = "delete from FingerPrint where FingerprintID=" + fingerprintID.ToString();
          return DBHelper.ExecuteNonQuery(sql);

      }

      public static DataTable GetFingerprintSettings()
      {
          string sql = "select * from FingerprintSettings";
          return DBHelper.GetDataSet(sql).Tables[0];

      }

      public static System.Data.SQLite.SQLiteDataAdapter GetFingerprintSettings(DataSet ds)
      {
          string sql = "select * from fingerprintsettings";
          return DBHelper.GetDataSet(sql, ds);
      }

      public static int AddVerifyInfo(string userId, string userName,  string userType,string userNumber, DateTime verifyTime,bool upload)
      {
          string sql = "insert into VerifyInfo (UserId,UserType,UserName,UserNumber,VerifyTime,Upload) values (@UserId,@UserType,@UserName,@UserNumber,@VerifyTime,@Upload)";

          SQLiteParameter[] param = new SQLiteParameter[6];
          param[0] = new SQLiteParameter("@UserId", userId);
          param[1] = new SQLiteParameter("@UserType", userType);
          param[2] = new SQLiteParameter("@UserName", userName);
          param[3] = new SQLiteParameter("@UserNumber", userNumber);
          param[4] = new SQLiteParameter("@VerifyTime", verifyTime.ToString("yyyy-MM-dd HH:mm:ss"));
          param[5] = new SQLiteParameter("@Upload", upload.ToString());

          return DBHelper.ExecuteSqlWithParams(sql, param);

         //string sql = string.Format("insert into VerifyInfo (UserId,FingerId,UserType,UserName,UserNumber,VerifyTime) values ({0},{1},{2},{3},{4})",userId,userType, userName, userNumber, verifyTime);
         
         // DBHelper.
      }

      public static DataTable GetMaxIdVerifyInfo()
      {
          string sql = "select * from VerifyInfo where VerifyInfoId=(select max(VerifyInfoId) from VerifyInfo)";
          return DBHelper.GetDataSet(sql).Tables[0];
      }

      public static DataTable GetMaxIdVerifyInfo(string UserId)
      {
          string sql = "select * from VerifyInfo where VerifyInfoId=(select max(VerifyInfoId) from VerifyInfo where UserId='" + UserId + "')";
          return DBHelper.GetDataSet(sql).Tables[0];
      }

      public static DataTable GetVerifyInfo(string userName, string userType, string userNumber, string verifyTime, string upload)
      {
          string sql="VerifyInfoId>0";
          if (userName != "") sql += " and UserName='" + userName + "'";
          if (userType != "") sql += " and UserType='" + userType + "'";
          if (userNumber != "") sql += " and UserNumber='" + userNumber + "'";
          if (verifyTime != "") sql += " and verifyTime like '" + verifyTime + "%'";
          if (upload != "") sql += " and Upload='" + upload + "'";

          sql = "select * from VerifyInfo where " + sql;

          return DBHelper.GetDataSet(sql).Tables[0];
      }

      public static int DelVerifyInfoById(int verifyInofId)
      {
          string sql = "delete from VerifyInfo where VerifyInfoId=" + verifyInofId.ToString();
          return DBHelper.ExecuteNonQuery(sql);
      }

      public static int UpdateVerifyInfoUpload(int verifyInofId,bool upload)
      {
          string sql = "update VerifyInfo set Upload='"+ upload.ToString() +"' where VerifyInfoId=" + verifyInofId.ToString();
          return DBHelper.ExecuteNonQuery(sql);
      }

        public static DataTable GetUserList(string UserType,string UserNumber,string UserName)
        {

            string Url = System.Configuration.ConfigurationSettings.AppSettings["GetUserListURL"];
            Url = string.Format("{0}?type={1}&number={2}&name={3}", Url, UserType, UserNumber, UserName);

            try
            {
                string ResultData = HttpPost.SendData(Url);

                Result users = Newtonsoft.Json.JsonConvert.DeserializeObject<Result>(ResultData);
                if (!users.success)
                    throw new Exception(users.msg);

                DataTable dt = new DataTable();
                dt.Columns.Add("UserType");
                dt.Columns.Add("UserId");
                dt.Columns.Add("UserName");
                dt.Columns.Add("UserNumber");
                foreach (User user in users.data)
                {
                    dt.Rows.Add(new object[] { user.type, user.id, user.name, user.number });
                }

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static bool UploadVerifyInfo(string UserType, string UserId,string VerifyTime)
        {
            string Url = System.Configuration.ConfigurationSettings.AppSettings["UploadVerifyInfoURL"];
            string PostData = string.Format("type={0}&id={1}", UserType, UserId);

            try
            {

                string ResultData = HttpPost.SendData(Url, PostData);

                Result reslut = Newtonsoft.Json.JsonConvert.DeserializeObject<Result>(ResultData);

                return reslut.success;
               
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static ManualVerifyResult ManualVerify(string UserId, string Password)
        {
            string Url = System.Configuration.ConfigurationSettings.AppSettings["ManualVerifyURL"];
            Url = string.Format("{0}?username={1}&password={2}", Url, UserId, Password);

            try
            {
                string ResultData = HttpPost.SendData(Url);

                ManualVerifyResult result = Newtonsoft.Json.JsonConvert.DeserializeObject<ManualVerifyResult>(ResultData);


                return result;
               
            }
            catch (Exception ex)
            {
                throw ex;
            }

           
        }
    }

    public class Result
    {
        public string msg="";
        public List<User> data = new List<User>();
        public bool success;
    }
    public class User
    {
        public int id;
        public string type;
        public string number;
        public string name;
    }

    public class ManualVerifyResult
    {
        public string msg = "";
        public string type;
        public string number;
        public string id;
        public string name;
        public bool success;
    }
}
